package com.situ.mall.controller;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.situ.mall.pojo.User;
import com.situ.mall.utils.JDBCUtil;
import com.situ.mall.utils.JSONUtil;
import com.situ.mall.vo.DeptCountVO;

// http://localhost:8080/Mall/hello.jsp
// http://localhost:8080/Mall/user
@WebServlet("/user")
public class UserServlet extends HttpServlet{

	//访问Servlet默认访问service方法
	// http://localhost:8080/Mall/user?method=selectAll
	// http://localhost:8080/Mall/user?method=deleteById&id=23
	@Override
	protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// JDBC
		System.out.println("UserServlet.service()");
		//解决post请求中文乱码问题
		req.setCharacterEncoding("utf-8");
		String method = req.getParameter("method");
		switch (method) {
		case "selectAll":
			selectAll(req, resp);
			break;
		case "deleteById":
			deleteById(req, resp);
			break;
		case "add":
			add(req, resp);
			break;
		case "selectDeptCount":
			selectDeptCount(req, resp);
			break;
		default:
			break;
		}
		
		
	}

	private void selectDeptCount(HttpServletRequest req, HttpServletResponse resp) {
		System.out.println("UserServlet.selectDeptCount()");
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<DeptCountVO> list = new ArrayList<DeptCountVO>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT d.name,count(*) as value\r\n" + 
					"FROM user as u INNER JOIN dept as d\r\n" + 
					"ON u.dept_id=d.id GROUP BY d.id";
			statement = connection.prepareStatement(sql);
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				String name = resultSet.getString("name");
				int value = resultSet.getInt("value");
				DeptCountVO deptCountVO = new DeptCountVO(name, value);
				list.add(deptCountVO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		for (DeptCountVO deptCountVO : list) {
			System.out.println(deptCountVO);
		}
		
		JSONUtil.array2Json(list, resp);
		
	}

	private void add(HttpServletRequest req, HttpServletResponse resp) throws IOException {
		System.out.println("UserServlet.add()");
		String username = req.getParameter("username");
		String password = req.getParameter("password");
		String email = req.getParameter("email");
		String role = req.getParameter("role");
		
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "insert into user(username,password,email,role) values(?,?,?,?)";
			statement = connection.prepareStatement(sql);
			statement.setString(1, username);
			statement.setString(2, password);
			statement.setString(3, email);
			statement.setInt(4, Integer.parseInt(role));
			System.out.println(statement);
			statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		//删除完之后，重定向到查找所有界面
		resp.sendRedirect(req.getContextPath() + "/user?method=selectAll");
	}

	private void deleteById(HttpServletRequest req, HttpServletResponse resp) throws IOException {
		System.out.println("UserServlet.deleteById()");
		String id = req.getParameter("id");
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "delete from user where id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, Integer.parseInt(id));
			System.out.println(statement);
			statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		//删除完之后，重定向到查找所有界面
		resp.sendRedirect(req.getContextPath() + "/user?method=selectAll");
	}

	private void selectAll(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<User> list = new ArrayList<User>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,dept_id,username,password,email,role from user";
			statement = connection.prepareStatement(sql);
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				//每遍历一次查出一个User
				int id = resultSet.getInt("id");
				int deptId = resultSet.getInt("dept_id");
				String username = resultSet.getString("username");
				String password = resultSet.getString("password");
				String email = resultSet.getString("email");
				int role = resultSet.getInt("role");
				User user = new User(id, deptId, username, password, email, role);
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		for (User user : list) {
			System.out.println(user);
		}
		
		//把查找出来的数据放到req域对象里面（一块内存区域）
		req.setAttribute("list", list);
		//转发到user_list.jsp页面进行展示
		req.getRequestDispatcher("/user_list.jsp").forward(req, resp);
	}
	
	
	
}
